import json
from pathlib import Path

import xlrd


class ReadExcel(object):

    def __init__(self):
        self.wb = ''
        self.sh = ''
        self.allnum = ''
        self.title = ''
        self.dicts_path = ''
        self.excel_path = ''

    def set_dicts_path(self, dicts_path):
        self.dicts_path = dicts_path

    def set_excel_path(self, path):
        self.excel_path = path

    def path_is_dir(self, path):
        """
        判断path 是否是目录
        :return:
        """
        if Path(path).is_dir() and Path(path).exists():
            return True
        return False

    def set_wb(self, filepath):
        """
        初始化excel对象
        """
        try:
            self.wb = xlrd.open_workbook(filepath)
        except:
            print("excel 错误" + str(filepath))

    def set_sh(self, num=0):
        """
        也可以 sheet_by_name
        初始化sheet对象,一般只有一个sheet
        """
        self.sh = self.wb.sheet_by_index(num)

    def read_line(self, num=0):
        """
        读取指定行
        """
        return self.sh.row_values(num)

    def read_title(self, num=0):
        self.title = self.read_line(num)

    def getlines(self):
        """
        获取行数
        """
        self.allnum = self.sh.nrows

    def create_dicts(self, num=1):
        """
        生成字典
        :return:
        """
        for rownum in range(num, self.allnum):
            rowvalue = self.sh.row_values(rownum)
            single = {}
            for colnum in range(0, len(rowvalue)):
                single[self.title[colnum]] = rowvalue[colnum]
            yield single

    def get_col_values(self, num):
        """
        获取列值
        """
        return self.sh.col_values(num)

    def add_file(self, num=1):
        with open(file=self.dicts_path, mode="a", encoding="utf-8") as f:
            for line in self.create_dicts(num):
                line = json.dumps(line, ensure_ascii=False)
                f.write(line + "\n")

    def deal_excel_file(self, file):
        self.set_wb(file)
        self.set_sh()
        self.read_title()
        self.getlines()
        self.add_file()

    def control1(self):
        """
        将excel 转换成json并写入大文件
        :return:
        """
        # 输入文件目录
        self.set_excel_path(r'./inputexcel')
        # 输出文件路径
        self.set_dicts_path(r'./outputdict/all.json')
        if self.path_is_dir(self.excel_path):
            for file in Path(self.excel_path).rglob('*.xlsx'):
                self.deal_excel_file(file)
        else:
            self.deal_excel_file(self.excel_path)

    def control(self):
        # 输入文件目录
        self.set_excel_path(r'D:\eiexcel\20190929')
        # 输出文件路径
        self.set_dicts_path(r'./AN_excel_json.txt')
        dicts = {}
        if self.path_is_dir(self.excel_path):
            for file in Path(self.excel_path).rglob('*.xlsx'):
                print(file)
                self.set_wb(file)
                self.set_sh()
                name = file.name
                Lists = []
                for value in self.get_col_values(1)[1:]:
                    Lists.append(value)
                dicts[name] = Lists
            jsons = json.dumps(dicts,ensure_ascii=False)
            with open(file=self.dicts_path, mode="a", encoding="utf-8") as f:
                f.write(jsons)


if __name__ == "__main__":
    read = ReadExcel()
    read.control()
